package DBLayer;

import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;

public class DBSupplier implements IFDBSupplier {
	private Connection con;

	public DBSupplier() {
		con = DbConnection.getInstance().getDBcon();
	}

	@Override
	public ArrayList<Supplier> getAllSuppliers(boolean retriveAssociation) {
		return miscWhere("", retriveAssociation);
	}

	@Override
	public Supplier findSupplierByID(int supID, boolean retriveAssociation) {
		String wClause = " supplierID = '" + supID + "'";
		return singleWhere(wClause, retriveAssociation);
	}

	@Override
	public Supplier searchByName(String name, boolean retriveAssociation) {
		String wClause = "name like '%" + name + "%'";
		System.out.println("SearchSupplier " + wClause);
		return singleWhere(wClause, retriveAssociation);
	}

	@Override
	public int updateSupplier(Supplier sup) {
		Supplier supObj = sup;
		int rc = -1;

		String query = "UPDATE supplier SET " + "name ='" + supObj.getName()
				+ "', " + "address ='" + supObj.getAddress() + "', "
				+ "country ='" + supObj.getCountry() + "', " + "phoneno ='"
				+ supObj.getPhoneNo() + "' " + "email = '" + supObj.getEmail()
				+ "'";
		System.out.println("Update query:" + query);
		try { // update supplier
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}// slut try
		catch (Exception ex) {
			System.out.println("Update exception in supplier db: " + ex);
		}
		return (rc);
	}

	@Override
	public int insertNewSupplier(Supplier sup) throws Exception {
		int rc = -1;
		String query = "INSERT INTO Supplier(name,address,country,phoneno,email) VALUES('"
				+ sup.getName()
				+ "','"
				+ sup.getAddress()
				+ "','"
				+ sup.getCountry()
				+ "','"
				+ sup.getPhoneNo()
				+ "','"
				+ sup.getEmail();
		System.out.println("insert : " + query);
		try { // insert new employee + dependent
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (SQLException ex) {
			System.out.println("Supplier ikke oprettet");
			throw new Exception("Supplier is not inserted correct");
		}
		return (rc);
	}

	private ArrayList<Supplier> miscWhere(String wClause,
			boolean retrieveAssociation) {
		ResultSet results;
		ArrayList<Supplier> list = new ArrayList<Supplier>();

		String query = buildQuery(wClause);

		try { // read the Supplier from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				Supplier supObj = new Supplier();
				supObj = buildSupplier(results);
				list.add(supObj);
			}// end while
			stmt.close();

		}// slut try
		catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	// Singelwhere is used when we only select one Supplier
	private Supplier singleWhere(String wClause, boolean retrieveAssociation) {
		ResultSet results;
		Supplier supObj = new Supplier();

		String query = buildQuery(wClause);
		System.out.println(query);
		try { // read the Supplier from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next()) {
				supObj = buildSupplier(results);
				// assocaition is to be build
				stmt.close();

			} else { // no Supplier was found
				supObj = null;
			}
		}// end try
		catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return supObj;
	}

	// method to build the query
	private String buildQuery(String wClause) {
		String query = "SELECT supplierID, name, address,country,phoneno,email FROM Supplier";

		if (wClause.length() > 0)
			query = query + " WHERE " + wClause;

		return query;
	}

	// method to build an Supplier object
	private Supplier buildSupplier(ResultSet results) {
		Supplier supObj = new Supplier();
		try { // the columns from the table emplayee are used
			supObj.setName(results.getString("name"));
			supObj.setAddress(results.getString("address"));
			supObj.setCountry(results.getString("country"));
			supObj.setPhoneNo(results.getString("phoneno"));
			supObj.setEmail(results.getString("email"));

		} catch (Exception e) {
			System.out.println("error in building the Supplier object");
		}
		return supObj;
	}

}
